﻿--2012-08-27
alter table [DatPost] add PostRank uniqueidentifier
--2012-06-27
--表名[DatDutyInfo]
alter table [DatDutyInfo] add HelpCode varchar(4)
增加一个辅助码
-- 2012-6-13
GO
/****** Object:  UserDefinedFunction [dbo].[fn_GetUserDeptUsers]    Script Date: 06/13/2012 17:45:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create  function [dbo].[fn_GetLoginNameDeptUsers](@Userid as uniqueidentifier)
returns @table_re table(UserId Varchar(50))
as  
begin
	insert @table_re select u.LoginName from datUser u,[dbo].[fn_GetUserDeptUsers](@Userid) b
	where u.UserId=b.UserId
	return 
end




----
--- 2012-05-22
----
CREATE TABLE [dbo].[EmployeePastTimeDepts](
	[ID] [uniqueidentifier] NOT NULL,
	[EmpID] [uniqueidentifier] NOT NULL,
	[DeptID] [uniqueidentifier] NOT NULL,
	[IsDept] [char](1) NULL,
 CONSTRAINT [PK_EmployeePastTimeDepts] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO
---
---2012-05-22
---
--use zx_app_dev
Create procedure [dbo].[sp_CreateDeptUser]
as 
begin
 select identity(int,1,1) as id,EmpID,DeptIds into # from DatEmployee 
--select * from # 
SELECT TOP 200000 id = identity(int,1,1) INTO #t FROM syscolumns a, syscolumns b 
--select EmpID,Cast(DeptID as uniqueidentifier)as deptID from( 
 delete from [EmployeePastTimeDepts]
--insert into [EmployeePastTimeDepts] 
select ID,EmpID, DeptID ,IsDept into #temp from(
  --select NEWID() as ID, EmpID,DeptID,N'N' as IsDept  from (
 SELECT  NEWID() as ID, A.EmpID,DeptID = SUBSTRING(A.DeptIds, B.ID, CHARINDEX(',', A.DeptIds + ',', B.ID) - B.ID),N'Y' as IsDept --这个SUBSTRING在B.ID每一次执行的时候的值是多少? 
  FROM # A, #t B 
   WHERE SUBSTRING(',' + a.DeptIds, B.id, 1) = ',' and a.deptids<>''    --这个where后面的检索条件又代表什么含义?  
   union  Select NEWID() as ID, EmpID,Cast(DeptID as Varchar(36)) as DeptID , N'Y' as IsDept  from DatEmployee
 ) a 
 insert into [EmployeePastTimeDepts]  select ID,EmpID, Cast(DeptID as  uniqueidentifier)as deptID,IsDept  from  #temp where len(DeptID)=36
 --)
 
-- select  EmpID,DeptID as c  from (
-- SELECT     A.EmpID,     DeptID = SUBSTRING(A.DeptIds, B.ID, CHARINDEX(',', A.DeptIds + ',', B.ID) - B.ID) --这个SUBSTRING在B.ID每一次执行的时候的值是多少? 
--  FROM # A, #t B 
--   WHERE SUBSTRING(',' + a.DeptIds, B.id, 1) = ',' and a.deptids<>'' and a.EmpId='3C37CA57-778E-4680-996E-9806B3E75BBF'    --这个where后面的检索条件又代表什么含义?  
--   union  Select EmpID,Cast(DeptID as Varchar(50)) DeptID from DatEmployee  where EmpId='3C37CA57-778E-4680-996E-9806B3E75BBF'  ) #temp
--  --where len(#temp.DeptID)=50
--   --group by EmpID
drop table #,#t

-- select distinct a.empid from (select * from #temp where LEN(deptid)=36) a
-- where deptid in
--(select deptid from #temp where LEN(deptid)=36 and EmpId='3C37CA57-778E-4680-996E-9806B3E75BBF') 
 
-- --select * from DatDepartment

End

--
--
--
--

GO

/****** Object:  UserDefinedFunction [dbo].[fn_GetUserDeptUsers]    Script Date: 05/23/2012 14:45:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		江怀玉
-- Create date: 2011-11-22
-- Modify date: 2011-5-22
-- Description:	当前人员的当前部门的所有人员
-- =============================================
CREATE function [dbo].[fn_GetUserDeptUsers](@Userid as uniqueidentifier)
returns @table_re table(UserId uniqueidentifier)
as  
begin
declare @EmpID uniqueidentifier
declare @DeptID uniqueidentifier
--select * from dbo.fn_GetOrganize('333FE8ED-CA2A-497A-A88C-3BB4FB3A1A57')
select @EmpID = e.EmpID,@DeptID=e.DeptID from DatEmployee e,DatUser u  where e.EmpID=u.EmpID and u.UserID=@UserID
insert into @table_re select distinct a.UserID from 
(select u.userID from [EmployeePastTimeDepts] e,DatUser u
where e.empId=u.EmpId and e.deptid in (
select DeptID from [EmployeePastTimeDepts] where EmpId=@EmpID
union select ID as DeptID from dbo.fn_GetOrganize(@DeptID)
)
) a
--declare @EmpId  uniqueidentifier
declare @CustomerId  uniqueidentifier
select  @EmpId = EmpId ,@CustomerId=CustomerId  from DatUser where UserID=@Userid
declare @deptCode varchar(100)
declare @DeptIds varchar(8000)
select  @deptCode = DeptPath from DatDepartment where DeptID=(select DeptID from DatEmployee where EmpId=@EmpID)
select  @DeptIds=DeptIds from DatEmployee where EmpID=@EmpId
--insert into @table_re
--SELECT     dbo.DatUser.UserID
--		FROM         dbo.DatUser INNER JOIN
--		  dbo.DatEmployee ON dbo.DatUser.EmpID = dbo.DatEmployee.EmpID INNER JOIN
--		  dbo.DatDepartment ON dbo.DatEmployee.DeptID = dbo.DatDepartment.DeptID
--		where dbo.DatDepartment.DeptPath like @deptCode+'%'
--union
--select UserId from dbo.fn_GetDeptsUsers(@DeptIds)
--SELECT     dbo.DatUser.UserID
--		FROM         dbo.DatUser INNER JOIN
--		  dbo.DatEmployee ON dbo.DatUser.EmpID = dbo.DatEmployee.EmpID and  dbo.DatUser.UserId = @UserId INNER JOIN
--		  dbo.DatDepartment ON dbo.DatEmployee.DeptIds like '%'+ cast(dbo.DatDepartment.DeptId as varchar(50))+'%'
--		--where 

--union 
--    select UserID from (SELECT    DatEmployee.EmpID,DatEmployee.Name,DatDepartment.DeptID,dbo.DatUser.UserID
--		  FROM         dbo.DatUser INNER JOIN
--		  dbo.DatEmployee ON dbo.DatUser.EmpID = dbo.DatEmployee.EmpID  INNER JOIN
--		  dbo.DatDepartment ON dbo.DatEmployee.DeptIds like '%'+ cast(dbo.DatDepartment.DeptId as varchar(50))+'%') temp
--		  where DeptID
--		  in (
--          SELECT     DatDepartment.DeptID
--		  FROM         dbo.DatUser INNER JOIN
--		  dbo.DatEmployee ON dbo.DatUser.EmpID = dbo.DatEmployee.EmpID and dbo.DatUser.UserId = @UserId INNER JOIN
--		  dbo.DatDepartment ON dbo.DatEmployee.DeptIds like '%'+ cast(dbo.DatDepartment.DeptId as varchar(50))+'%'
--		  )		
--union
insert into @table_re 
SELECT     dbo.DatUser.UserID
		FROM   dbo.DatUser INNER JOIN
		  dbo.TrCustomer ON dbo.DatUser.CustomerID = dbo.TrCustomer.TrCustomerID INNER JOIN
		  dbo.DatDepartment ON dbo.TrCustomer.DeptID = dbo.DatDepartment.DeptID	
union
SELECT     dbo.DatUser.UserID
		FROM   dbo.DatUser INNER JOIN
		  dbo.TrCustomer ON dbo.DatUser.CustomerID = dbo.TrCustomer.TrCustomerID INNER JOIN
		  dbo.DatDepartment ON dbo.TrCustomer.DeptID = dbo.DatDepartment.DeptID
		--where dbo.DatDepartment.DeptId like @deptCode+'%'
		where dbo.TrCustomer.AddUser =@Userid
union
SELECT     dbo.DatUser.UserID
		FROM         dbo.DatUser INNER JOIN
		  dbo.TrCustomer ON dbo.DatUser.CustomerID = dbo.TrCustomer.TrCustomerID INNER JOIN
		  dbo.DatDepartment ON dbo.TrCustomer.DeptID = dbo.DatDepartment.DeptID
		--where dbo.DatDepartment.DeptId like @deptCode+'%'
		where dbo.DatDepartment.DeptID=(select DeptID from TrCustomer where TrCustomerID=@CustomerId)
return 
end

GO





---
--


--2012-05-11
USE [zx_app_dev]
GO

CREATE TABLE [dbo].[EmployeeDeptsUsers](
	[ID] [uniqueidentifier] NOT NULL,
	[UserID] [uniqueidentifier] NULL,
	[OwnerUserID] [uniqueidentifier] NULL,
 CONSTRAINT [PK_EmployeeDeptsUsers] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[EmployeeDeptsUsers] ADD  CONSTRAINT [DF_EmployeeDeptsUsers_ID]  DEFAULT (newid()) FOR [ID]
GO


--表名:[SysLib]
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[SysLib](
	[LibID] [uniqueidentifier] NOT NULL,
	[LibName] [varchar](150) NULL,
	[LibType] [varchar](50) NULL,
	[AddUser] [uniqueidentifier] NULL,
	[AddTime] [datetime] NULL,
 CONSTRAINT [PK_SysLib] PRIMARY KEY CLUSTERED 
(
	[LibID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[SysLib] ADD  CONSTRAINT [DF_SysLib_LibID]  DEFAULT (newid()) FOR [LibID]
GO
------------------------------------------------------------------------




--2012-4-11
alter view SysFunction as 
SELECT DISTINCT 
      dbo.DatUser.UserID, dbo.SysFunction.FunctionId, dbo.SysFunction.Name, 
      dbo.SysFunction.ParentID, dbo.SysFunction.FunctionPath, dbo.SysFunction.URL, 
      dbo.SysFunction.Type, dbo.SysFunction.Icon, dbo.SysFunction.ShowOrder, 
      dbo.SysFunction.Remark, dbo.SysFunction.IsMenu
FROM dbo.DatUser INNER JOIN
      dbo.SysUserRole ON dbo.DatUser.UserID = dbo.SysUserRole.UserID INNER JOIN
      dbo.SysRole ON dbo.SysUserRole.RoleID = dbo.SysRole.RoleID INNER JOIN
      dbo.SysRoleFunction ON 
      dbo.SysRole.RoleID = dbo.SysRoleFunction.RoleID INNER JOIN
      dbo.SysFunction ON dbo.SysRoleFunction.FunctionID = dbo.SysFunction.FunctionId



--2012-4-5
alter table SysFunction add IsMenu char(1)
alter table SysFunction add EntityName varchar(100)

--2012-03-25
--表名[Set_DataListTemplate]
alter table [Set_DataListTemplate] add IsQuery char(1)
alter table [Set_Entity] add BLLConfig varchar(1000)



USE [zx_app_dev]
GO
/****** 对象:  UserDefinedFunction [dbo].[fn_GetUserDeptUsers]    脚本日期: 02/29/2012 18:18:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		江怀玉
-- Create date: 2011-11-22
-- Description:	当前人员的当前部门的所有人员
-- =============================================
create function [dbo].[fn_GetDeptsUsers](@Depts as varchar(8000))
returns @table_re table(UserId uniqueidentifier)
as  
begin
insert into @table_re
SELECT     DatUser.UserID
		FROM         DatUser INNER JOIN
		  DatEmployee ON DatUser.EmpID = DatEmployee.EmpID INNER JOIN
		  DatDepartment ON DatEmployee.DeptID = DatDepartment.DeptID
		where DatDepartment.DeptID in 
( select DeptID from fn_splitDeptID(@Depts,','))
union
SELECT     dbo.DatUser.UserID
		FROM         dbo.DatUser INNER JOIN
		  dbo.TrCustomer ON dbo.DatUser.CustomerID = dbo.TrCustomer.TrCustomerID INNER JOIN
		  dbo.DatDepartment ON dbo.TrCustomer.DeptID = dbo.DatDepartment.DeptID
		--where dbo.DatDepartment.DeptId like @deptCode+'%'
		where dbo.DatDepartment.DeptID in (  select DeptID from fn_splitDeptID(@Depts,','))
return 
end






GO
/****** 对象:  UserDefinedFunction [dbo].[fn_GetUserDeptUsers]    脚本日期: 02/29/2012 17:44:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		江怀玉
-- Create date: 2011-11-22
-- Description:	当前人员的当前部门的所有人员
-- =============================================
ALTER function [dbo].[fn_GetUserDeptUsers](@Userid as uniqueidentifier)
returns @table_re table(UserId uniqueidentifier)
as  
begin
declare @EmpId  uniqueidentifier
declare @CustomerId  uniqueidentifier
select @EmpId = EmpId ,@CustomerId=CustomerId  from DatUser where UserID=@Userid
declare @deptCode varchar(100)
select @deptCode = DeptPath from DatDepartment where DeptID=(select DeptID from DatEmployee where EmpId=@EmpID)

insert into @table_re
SELECT     dbo.DatUser.UserID
		FROM         dbo.DatUser INNER JOIN
		  dbo.DatEmployee ON dbo.DatUser.EmpID = dbo.DatEmployee.EmpID INNER JOIN
		  dbo.DatDepartment ON dbo.DatEmployee.DeptID = dbo.DatDepartment.DeptID
		where dbo.DatDepartment.DeptPath like @deptCode+'%'
union
SELECT     dbo.DatUser.UserID
		FROM         dbo.DatUser INNER JOIN
		  dbo.DatEmployee ON dbo.DatUser.EmpID = dbo.DatEmployee.EmpID INNER JOIN
		  dbo.DatDepartment ON dbo.DatEmployee.DeptIds like '%'+ cast(dbo.DatDepartment.DeptId as varchar(50))+'%'
		where dbo.DatUser.UserId = @UserId
union
SELECT     dbo.DatUser.UserID
		FROM   dbo.DatUser INNER JOIN
		  dbo.TrCustomer ON dbo.DatUser.CustomerID = dbo.TrCustomer.TrCustomerID INNER JOIN
		  dbo.DatDepartment ON dbo.TrCustomer.DeptID = dbo.DatDepartment.DeptID	
union
SELECT     dbo.DatUser.UserID
		FROM   dbo.DatUser INNER JOIN
		  dbo.TrCustomer ON dbo.DatUser.CustomerID = dbo.TrCustomer.TrCustomerID INNER JOIN
		  dbo.DatDepartment ON dbo.TrCustomer.DeptID = dbo.DatDepartment.DeptID
		--where dbo.DatDepartment.DeptId like @deptCode+'%'
		where dbo.TrCustomer.AddUser =@Userid
union
SELECT     dbo.DatUser.UserID
		FROM         dbo.DatUser INNER JOIN
		  dbo.TrCustomer ON dbo.DatUser.CustomerID = dbo.TrCustomer.TrCustomerID INNER JOIN
		  dbo.DatDepartment ON dbo.TrCustomer.DeptID = dbo.DatDepartment.DeptID
		--where dbo.DatDepartment.DeptId like @deptCode+'%'
		where dbo.DatDepartment.DeptID=(select DeptID from TrCustomer where TrCustomerID=@CustomerId)
return 
end







GO
/****** 对象:  UserDefinedFunction [dbo].[fn_splitstring]    脚本日期: 02/29/2012 17:06:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*创建分割部门ID的方法*/
create function [dbo].[fn_splitDeptID] 
( 
	@inputstr varchar(8000), 
	@seprator char(1) 
) 
returns @temp table (DeptID  uniqueidentifier ) 
as

begin 
declare @i int 

set @inputstr = rtrim(ltrim(@inputstr)) 
set @i = charindex(@seprator, @inputstr)

while @i >= 1 
begin 
insert @temp values(left(@inputstr, @i - 1))

set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i) 
set @i = charindex(@seprator, @inputstr) 
end 

if @inputstr <> '' 
insert @temp values(cast(@inputstr as uniqueidentifier))

return 
end 





--2012-02-27
--表名SysRoleFunction
alter table [SysRoleFunction] add Depts varchar(1000)
alter table [SysRoleFunction] add Condition varchar(1000)


--2012-02-22 用于生成TreeCode
GO
/****** Object:  UserDefinedFunction [dbo].[fn_Sys_GetTree]    Script Date: 02/21/2012 10:03:17 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

-- Drop FUNCTION  Dnff_sys_GetTree
create FUNCTION  [dbo].[fn_Sys_GetTree](
/*
   delete Dnft_tra_TreeCode
   insert Dnft_tra_TreeCode(key_seq,link_key_seq,Tree_Name,SortID) select key_seq,link_key_seq,menu_name,SortID from Dnft_sys_Menu
   select * from dbo.Dnff_sys_GetTree('root',100,'   ','')
   delete Dnft_tra_TreeCode
*/
@key_seq_P  varchar(Max),
@iLevel  int,
@Tree_NameFill_P  varchar(Max),
@Tree_Code_P  varchar(Max)
)RETURNS @re TABLE(Tree_Name nvarchar(Max),Tree_Code nvarchar(Max),key_seq varchar(50),SortID int)

AS   
BEGIN   

declare @Tree_Name nvarchar (100)  
declare @key_seq varchar (50)   
declare @iLevelIndex  int;set @iLevelIndex=0
declare crRoll cursor for   
select Tree_Name,key_seq from Dnft_tra_TreeCode (nolock) where link_key_seq=@key_seq_P order by SortID asc,up_dtime desc

open crRoll  
 while 1=1  --@i<@count  
   begin   
      fetch next from crRoll into  @Tree_Name,@key_seq
      if @@fetch_status <> 0 break  
         begin
               declare @Tree_Code varchar(Max);
               set @Tree_Code=case when @Tree_Code_P=''
                              then '@-'+dbo.fn_sql_IntToString(@iLevelIndex+1,4)
                              else @Tree_Code_P+'-'+dbo.fn_sql_IntToString(@iLevelIndex+1,4)
                              end
               
               -- Add Node
               insert @re values(@Tree_NameFill_P+@Tree_Name,@Tree_Code ,@key_seq,-1)
               
               -- Call MySelf
               if (select count(*) from Dnft_tra_TreeCode (nolock) where link_key_seq=@key_seq)>0 and @iLevelIndex<@iLevel
                 begin
                    declare @iLevelSub int;set @iLevelSub=@iLevel-1
                    insert into @re 
                      select isnull(@Tree_NameFill_P+Tree_Name,''),Tree_Code ,key_seq,-1
                      from dbo.fn_Sys_GetTree(@key_seq,@iLevelSub,@Tree_NameFill_P,@Tree_Code)
                 end
              set @iLevelIndex=@iLevelIndex+1
        end  
   end   
 close crRoll
 deallocate crRoll

--更新SortID 					   
update @re set SortID=convert(int,case when len(isnull(tree_Code,'-1'))<6 then '-1' else right(tree_Code,4) end)

RETURN   
END


GO
/****** Object:  UserDefinedFunction [dbo].[fn_sql_IntToString]    Script Date: 02/21/2012 10:03:07 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO


-- Drop FUNCTION  Dnff_sql_IntToString
create FUNCTION  [dbo].[fn_sql_IntToString](
/*
      select dbo.Dnff_sql_IntToString(8,2) 
*/
@iInput int,
@iCount int
) returns varchar(16)  

AS   
begin  
    declare @rtn_string varchar(255)  +
    declare @iAll int; set @iAll=1
 
	declare @i int 
	set @i=1 
	while @i<@iCount+1 
	begin 
       set @iAll = @iAll * 10;
	   set @i=@i+1 
	end 
    set @iAll=@iAll+@iInput

    set @rtn_string=right(convert(varchar(16),@iAll),@iCount);
    
    return @rtn_string  
end







